package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.maszy.bean.Product;
import com.maszy.db.DBConnection;

public class ProductService {
	private static PreparedStatement pst;
	private static ResultSet rs = null;
	public  int add(Product obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql ="insert into Maszy_Product(Maszy_Product_Name,Maszy_Product_Specification,Maszy_Product_Unit,Maszy_Product_Dept,Maszy_Product_Status,Maszy_Product_Remark,Maszy_Product_Sort) values(?,?,?,?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			pst.setString(1,obj.getProduct_Name());
			pst.setString(2, obj.getProduct_Specification());
			pst.setString(3,obj.getProduct_Unit());
			pst.setInt(4,obj.getProduct_Dept_Id());
			pst.setString(5,obj.getProduct_Status());			
			pst.setString(6, obj.getProduct_Remark());
			pst.setInt(7, obj.getProduct_Sort());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}
	public int update(Product obj){
		DBConnection con = new DBConnection();
		int i=0;
		String sql="update Maszy_Product set Maszy_Product_Name=?,Maszy_Product_Specification=?,Maszy_Product_Unit=?,Maszy_Product_Dept=?,Maszy_Product_Status=?,Maszy_Product_Remark=?,Maszy_Product_Sort=? where Maszy_Product_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);			
			pst.setString(1,obj.getProduct_Name());
			pst.setString(2, obj.getProduct_Specification());
			pst.setString(3,obj.getProduct_Unit());
			pst.setInt(4,obj.getProduct_Dept_Id());
			pst.setString(5,obj.getProduct_Status());			
			pst.setString(6, obj.getProduct_Remark());
			pst.setInt(7, obj.getProduct_Sort());
			pst.setLong(8, obj.getProduct_Id());
			i=pst.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}
	public int delete(Product obj){
		DBConnection con = new DBConnection();
		int i=0;
		String sql="delete from  Maszy_Product  where Maszy_Product_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);	
			pst.setLong(1, obj.getProduct_Id());
			i=pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}
	public Product queryById(Product obj){
		DBConnection con = new DBConnection();
		String sql="select a.*,b.* from Maszy_Product a,Maszy_Dept b where a.Maszy_Product_Id=? and a.Maszy_Product_Dept=b.dept_id";
		try {
			pst = con.getConnection().prepareStatement(sql);	
			pst.setLong(1, obj.getProduct_Id());
			rs = pst.executeQuery();
			while (rs.next()) {
				obj=new Product();
				obj.setProduct_Id(rs.getLong("Maszy_Product_Id"));
				obj.setProduct_Name(rs.getString("Maszy_Product_Name"));
				obj.setProduct_Specification(rs.getString("Maszy_Product_Specification"));
				obj.setProduct_Unit(rs.getString("Maszy_Product_Unit"));
				obj.setProduct_Dept_Id(rs.getInt("Maszy_Product_Dept"));
				obj.setProduct_Dept_Name(rs.getString("dept_name"));
				obj.setProduct_Status(rs.getString("Maszy_Product_Status"));
				obj.setProduct_Remark(rs.getString("Maszy_Product_Remark"));	
				obj.setProduct_Sort(rs.getInt("Maszy_Product_Sort"));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}
	public List<Product> queryAll(){
		List<Product> list=new ArrayList<Product>();
		DBConnection con = new DBConnection();
		String sql="select a.*,b.* from Maszy_Product a,Maszy_Dept b where a.Maszy_Product_Dept=b.dept_id order by a.Maszy_Product_Sort";
		try {
			pst = con.getConnection().prepareStatement(sql);				
			rs = pst.executeQuery();
	
			while (rs.next()) {
				Product obj=new Product();
				obj.setProduct_Id(rs.getLong("Maszy_Product_Id"));
				obj.setProduct_Name(rs.getString("Maszy_Product_Name"));
				obj.setProduct_Specification(rs.getString("Maszy_Product_Specification"));
				obj.setProduct_Unit(rs.getString("Maszy_Product_Unit"));
				obj.setProduct_Dept_Id(rs.getInt("Maszy_Product_Dept"));
				obj.setProduct_Dept_Name(rs.getString("dept_name"));
				obj.setProduct_Status(rs.getString("Maszy_Product_Status"));
				obj.setProduct_Remark(rs.getString("Maszy_Product_Remark"));
				obj.setProduct_Sort(rs.getInt("Maszy_Product_Sort"));
				
				list.add(obj);
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
}
